﻿using CreateCodeByDatabase.Bll;
using CreateCodeByDatabase.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace CreateCodeByDatabase
{
    public partial class UpdateDatabase : Form
    {
        IOperate operate { get; set; }
        /// <summary>
        /// 表字典，格式[表名,{列信息}]
        /// </summary>
        private Dictionary<string, List<ColumnInfo>> tableColumnDic;
        /// <summary>
        /// 已读取PDM文件
        /// </summary>
        XmlDocument projectXmlFile;
        public UpdateDatabase()
        {
            operate = IOperate.GetOperate();
            InitializeComponent();
        }

        private void UpdateDatabase_Load(object sender, EventArgs e)
        {
            txtTableName.Text = "t_hj_";
            txtFilePath.Text = @"D:\Work\Source\FKMMS\Requirement_Doc\横机数据库设计\申洲横机管理系统_4_25.pdm";
        }
        /// <summary>
        /// 选择PDM文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearchPDMFile_Click(object sender, EventArgs e)
        {
            openFileDialog1.AddExtension = true;
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.Filter = "pdm文件|*.pdm";
            openFileDialog1.CheckPathExists = true;
            openFileDialog1.DefaultExt = ".pdm";
            openFileDialog1.InitialDirectory = @"D:\Work\Source\FKMMS\Requirement_Doc\横机数据库设计";
            if (openFileDialog1.ShowDialog() != DialogResult.OK)
                return;
            txtFilePath.Text = openFileDialog1.FileName;
        }
        /// <summary>
        /// 读取PDM文件中的表信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnQuery_Click(object sender, EventArgs e)
        {
            projectXmlFile = GetXmlDocument();
            if(projectXmlFile == null)
            {
                MessageBox.Show("载入PDM文件失败");
                return;
            }
            tableColumnDic = new Dictionary<string, List<ColumnInfo>>();
            List<TableInfo> tableList = new List<TableInfo>();
            try
            {
                ReadTableInfo(tableColumnDic, tableList);
                //if (tableColumnDic.Count < 1 || tableList.Count < 1)
                //{
                //    MessageBox.Show("读取PDM文件表信息失败");
                //    return;
                //}
                gridMain.DataSource = tableList;
            }
            catch (Exception ex)
            {
                Log.WriteLog(ex);
                MessageBox.Show("执行异常，详细信息请查看日志");
            }
        }
        /// <summary>
        /// 载入读取PDM文件
        /// </summary>
        /// <returns></returns>
        private XmlDocument GetXmlDocument()
        {
            XmlDocument projectXmlFile = new XmlDocument();
            try
            {
                projectXmlFile.Load(txtFilePath.Text);
                return projectXmlFile;
            }
            catch (Exception ex)
            {
                Log.WriteLog(ex);
                return null;
            }
        }
        /// <summary>
        /// 读取表信息
        /// </summary>
        /// <param name="projectXmlFile"></param>
        /// <param name="tableColumnDic"></param>
        /// <param name="tableList"></param>
        private void ReadTableInfo(Dictionary<string, List<ColumnInfo>> tableColumnDic,List<TableInfo> tableList)
        {
            string queryCondition = txtTableName.Text.Trim().ToUpper();
            //项目文档中使用了命名空间 ，使用XPath时 只有包含命名空间信息才可查询到节点信息
            string xPath = @"/Model/o:RootObject/c:Children/o:Model/c:Tables";
            //string xPath = @"o:RootObject/c:Children/o:Model/c:Tables";
            XmlNamespaceManager nsmgr = new System.Xml.XmlNamespaceManager(projectXmlFile.NameTable);
            nsmgr.AddNamespace("c", "collection");
            nsmgr.AddNamespace("o", "object");
            nsmgr.AddNamespace("a", "attribute");

            XmlNode tablesNode = projectXmlFile.DocumentElement.SelectSingleNode(xPath, nsmgr);
            if (tablesNode == null || tablesNode.ChildNodes == null || tablesNode.ChildNodes.Count < 1)
            {
                MessageBox.Show("读取PDM文件表节点失败");
                return;
            }

            List<string> errorList = new List<string>();
            int tempErrorCount = 0;
            string tempxPath = null;
            string tempTableName = null;
            foreach (XmlNode itemTable in tablesNode.ChildNodes)
            {
                tempxPath = "a:Code";
                System.Xml.XmlNode tableNameNode = itemTable.SelectSingleNode(tempxPath, nsmgr);
                tempTableName = tableNameNode.InnerText.ToUpper();
                if (!string.IsNullOrWhiteSpace(queryCondition) && !tempTableName.StartsWith(queryCondition))
                    continue;

                tempxPath = "a:Name";
                System.Xml.XmlNode tableCommentNode = itemTable.SelectSingleNode(tempxPath, nsmgr);
                TableInfo entityTable = new TableInfo();
                entityTable.Comment = tableCommentNode.InnerText;
                entityTable.TableName = tempTableName;
                tableList.Add(entityTable);
                
                tempxPath = "c:Columns";
                XmlNode ColumnNodeParent = itemTable.SelectSingleNode(tempxPath, nsmgr);
                foreach (XmlNode itemColumn in ColumnNodeParent.ChildNodes)
                {
                    tempxPath = "a:Code";
                    System.Xml.XmlNode columnNameNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    tempxPath = "a:Name";
                    System.Xml.XmlNode columnCommentNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    tempxPath = "a:DataType";
                    System.Xml.XmlNode columnDataTypeNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    if(columnDataTypeNode == null || columnDataTypeNode.InnerText.ToUpper() == "UNDEFINED")
                    {
                        tempErrorCount++;
                        //errorList.Add($"表名:{tempTableName} 字段：{columnCommentNode.InnerText} {columnNameNode.InnerText}异常 数据类型：{(columnDataTypeNode == null?"":columnDataTypeNode.InnerText)}");
                        //continue;
                    }
                    ColumnInfo entityColumn = new ColumnInfo();
                    if(columnNameNode != null)
                        entityColumn.ColumnName = columnNameNode.InnerText.ToUpper();
                    if(columnCommentNode != null)
                        entityColumn.Comment = columnCommentNode.InnerText;
                    if(columnDataTypeNode != null)
                        entityColumn.DataType = columnDataTypeNode.InnerText;
                    entityColumn.TableName = tempTableName;

                    if (tableColumnDic.ContainsKey(tempTableName))
                        tableColumnDic[tempTableName].Add(entityColumn);
                    else
                        tableColumnDic.Add(tempTableName,new List<ColumnInfo>() { entityColumn });      
                }
            }
            if (errorList.Count > 0)
                MessageBox.Show(string.Join(",", errorList));
        }
        /// <summary>
        /// 全选
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkSelectAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                IEnumerable<TableInfo> entityList = gridMain.DataSource as IEnumerable<TableInfo>;
                if (entityList == null || entityList.Count() < 1)
                {
                    return;
                }
                foreach (var item in entityList)
                {
                    item.IsChoice = chkSelectAll.Checked;
                }
                gridMain.Refresh();
            }
            catch (Exception ex)
            {
                Log.WriteLog(ex);
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// 更新数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if(tableColumnDic == null || tableColumnDic.Count < 1)
            {
                MessageBox.Show("请先读取PDM文件");
                return;
            }
            IEnumerable<TableInfo> dataList = gridMain.DataSource as IEnumerable<TableInfo>;
            var tempDataList = dataList.Where(Item => Item.IsChoice).ToList();
            if(tempDataList.Count() < 1)
            {
                MessageBox.Show("请选择待更新表");
                return;
            }
            List<string> commentList = new List<string>();
            string sqlInfo = PackageSql(tableColumnDic, tempDataList, commentList);
            if(operate.ExecuteSql(sqlInfo, commentList) == 0 )
                MessageBox.Show("更新数据库失败");
            else
                MessageBox.Show("更新数据库成功");
        }
        /// <summary>
        /// 组装Sql
        /// </summary>
        /// <param name="tableColumnDic"></param>
        /// <param name="tableList"></param>
        /// <returns></returns>
        private string PackageSql(Dictionary<string, List<ColumnInfo>> tableColumnDic, List<TableInfo> tableList, List<string> commentList)
        {
            List<string> tableNameList = tableList.Select(Item=>Item.TableName).ToList();
            StringBuilder resultSql = new StringBuilder();
            resultSql.AppendLine("declare");
            resultSql.AppendLine("cot number := 0;");
            resultSql.AppendLine("begin");

            string sqlExist = "select count(1) into cot from user_tables where table_name ='{0}';";
            string sqlDropTable = "execute immediate 'drop table {0}';";
            foreach (var itemTable in tableColumnDic)
            {
                if (!tableNameList.Contains(itemTable.Key))
                    continue;
                resultSql.AppendFormat(sqlExist, itemTable.Key);
                resultSql.AppendLine();
                resultSql.AppendLine("if cot > 0 then");
                resultSql.AppendLine(string.Format(sqlDropTable, itemTable.Key));
                resultSql.AppendLine("end if;");
                resultSql.AppendLine(string.Format("execute immediate 'create table {0}", itemTable.Key));
                resultSql.AppendLine("(");
                for (int i = 0; i < itemTable.Value.Count; i++)
                {
                    if (i != itemTable.Value.Count - 1)
                        resultSql.AppendLine(string.Format("\t{0} {1},", itemTable.Value[i].ColumnName, itemTable.Value[i].DataType));
                    else
                        resultSql.AppendLine(string.Format("\t{0} {1}", itemTable.Value[i].ColumnName, itemTable.Value[i].DataType));
                    if (!string.IsNullOrWhiteSpace(itemTable.Value[i].Comment))
                        commentList.Add(string.Format("comment on column {0}.{1} is '{2}'", itemTable.Key, itemTable.Value[i].ColumnName, itemTable.Value[i].Comment));
                }
                resultSql.AppendLine(")';");
            }
            foreach (var item in tableList)
            {
                if (!string.IsNullOrWhiteSpace(item.Comment))
                    commentList.Add(string.Format("comment on table {0} is '{1}'", item.TableName, item.Comment));
            }
            resultSql.AppendLine("end;");
            return resultSql.ToString();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            IEnumerable<TableInfo> dataList = gridMain.DataSource as IEnumerable<TableInfo>;
            var tempDataList = dataList.Where(Item => Item.IsChoice).ToList();
            if (tempDataList.Count() < 1)
            {
                MessageBox.Show("请选择表");
                return;
            }
            if (tempDataList.Count() != 1)
            {
                MessageBox.Show("请选择1个表");
                return;
            }
            if (tableColumnDic == null || tableColumnDic.Count < 1)
            {
                MessageBox.Show("请先读取PDM文件");
                return;
            }
            string xPath = @"/Model/o:RootObject/c:Children/o:Model/c:Tables";
            XmlNamespaceManager nsmgr = new System.Xml.XmlNamespaceManager(projectXmlFile.NameTable);
            nsmgr.AddNamespace("c", "collection");
            nsmgr.AddNamespace("o", "object");
            nsmgr.AddNamespace("a", "attribute");
            XmlNode tablesNode = projectXmlFile.DocumentElement.SelectSingleNode(xPath, nsmgr);

            try
            {
                foreach (var entity in tempDataList)
                {
                    if (!tableColumnDic.ContainsKey(entity.TableName))
                    {
                        MessageBox.Show("选择表异常,PDM文件中无该表信息");
                        return;
                    }
                    List<ColumnInfo> columnList = operate.GetTableColumnList(new List<string>() { entity.TableName }).ToList();
                    if (columnList.Count() != tableColumnDic[entity.TableName].Count)
                    {
                        MessageBox.Show("选择表异常,表字段数量与PDM文件该表字段数量不等");
                        return;
                    }
                    UpdatePDMFile(tablesNode, nsmgr, entity.TableName, columnList);
                    projectXmlFile.Save(txtFilePath.Text);
                }
            }
            catch (Exception ex)
            {
                Log.WriteLog(ex);
                MessageBox.Show(ex.Message);
            }
            MessageBox.Show("更新成功");
        }
        private void UpdatePDMFile(XmlNode tablesNode, XmlNamespaceManager nsmgr, string tableName, List<ColumnInfo> columnList)
        {
            string tempxPath = null;
            string tempTableName = null;
            int tempIndex = 0;
            string dataLength = null;
            string precision = null;
            foreach (XmlNode itemTable in tablesNode.ChildNodes)
            {
                tempxPath = "a:Code";
                System.Xml.XmlNode tableNameNode = itemTable.SelectSingleNode(tempxPath, nsmgr);
                tempTableName = tableNameNode.InnerText.ToUpper();
                if (tableName != tempTableName)
                    continue;

                tempIndex = -1;
                tempxPath = "c:Columns";
                XmlNode ColumnNodeParent = itemTable.SelectSingleNode(tempxPath, nsmgr);
                foreach (XmlNode itemColumn in ColumnNodeParent.ChildNodes)
                {
                    tempIndex++;
                    tempxPath = "a:Name";
                    System.Xml.XmlNode columnCommentNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    columnCommentNode.InnerText = columnList[tempIndex].ColumnName;
                    tempxPath = "a:Code";
                    System.Xml.XmlNode columnNameNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    columnNameNode.InnerText = columnList[tempIndex].ColumnName;
                    tempxPath = "a:DataType";
                    System.Xml.XmlNode columnDataTypeNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                    if (columnDataTypeNode == null || columnDataTypeNode.InnerText.ToUpper() == "UNDEFINED")
                    {
                        columnDataTypeNode = projectXmlFile.CreateElement(tempxPath, "a");
                        itemColumn.AppendChild(columnDataTypeNode);
                    }
                    columnDataTypeNode.InnerText = GetDataType(columnList[tempIndex],out dataLength,out precision);
                    if(!string.IsNullOrWhiteSpace(dataLength))
                    {
                        tempxPath = "a:Length";
                        XmlNode columnDataLengthNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                        if(columnDataLengthNode == null)
                        {
                            columnDataLengthNode = projectXmlFile.CreateElement(tempxPath, "a");
                            itemColumn.AppendChild(columnDataLengthNode);
                        }
                        columnDataLengthNode.InnerText = dataLength;
                    }
                    if (!string.IsNullOrWhiteSpace(precision))
                    {
                        tempxPath = "a:Precision";
                        XmlNode columnDataPrecisionLengthNode = itemColumn.SelectSingleNode(tempxPath, nsmgr);
                        if (columnDataPrecisionLengthNode == null)
                        {
                            columnDataPrecisionLengthNode = projectXmlFile.CreateElement(tempxPath, "a");
                            itemColumn.AppendChild(columnDataPrecisionLengthNode);
                        }
                        columnDataPrecisionLengthNode.InnerText = precision;
                    }
                }
            }
        }
        private string GetDataType(ColumnInfo column,out string dataLength,out string precision)
        {
            string columnDataType = null;
            dataLength = null;
            precision = null;
            switch (column.DataType)
            {
                case "NVARCHAR2":
                case "CHAR":
                    columnDataType = $"{column.DataType}({column.DataLength})";
                    dataLength = column.DataLength.ToString();
                    break;
                case "DATE":
                    columnDataType = column.DataType;
                    break;
                case "NUMBER":
                    if (column.DataScale.HasValue)
                    {
                        if(column.DataScale.Value > 0)
                        {
                            columnDataType = $"{column.DataType}({column.DataPrecision.Value},{column.DataScale.Value})";
                            dataLength = column.DataPrecision.Value.ToString();
                            precision = column.DataScale.Value.ToString();
                        }
                        else if(column.DataScale.Value == 0)
                        {
                            if(column.DataPrecision.HasValue)
                            {
                                columnDataType = $"{column.DataType}({column.DataPrecision.Value})";
                                dataLength = column.DataPrecision.Value.ToString();
                            }
                            else
                                columnDataType = $"{column.DataType}(INTEGER)";
                        }
                    }
                    else
                        throw new Exception(column.ColumnName + "数据类型异常");
                    break;
                default:
                    throw new Exception(column.ColumnName + "数据类型遗漏");
                    break;
            }
            return columnDataType;
        }
    }
}
